#!/usr/bin/env python
# -*- coding: utf-8 -*-

###
# Wifi + GPS fingerprint collection tool
# Copyright (c) 2008, Yura Zenevich, Jorge Silva, and Jamon Camisso
# Homepage http://wiki.scyp.atrc.utoronto.ca/wiki/WiFiPositioning
# Contact scyp@atrc.utoronto.ca
#    
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program, see enclosed file gpl-3.txt or browse to
# <http://www.gnu.org/licenses/gpl.txt>
###

import string
import sqlite3

class Addresses:
	def GetExtent(self, floorNumber, buildingName):
		connection=sqlite3.connect("Addresses.db")
		try:
			extent = connection.execute("SELECT FP.Extent FROM Buildings AS B INNER JOIN BuildingFloors AS BF ON B.BuildingId = BF.BuildingId INNER JOIN FloorPlans AS FP ON FP.BuildingFloorId = BF.BuildingFloorId WHERE B.BuildingName = ? AND BF.FloorNumber = ?;", (buildingName, floorNumber)).fetchone()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		return extent

	def ReturnListOfBuildings(self):
		connection=sqlite3.connect("Addresses.db")
		try:
			buildings = connection.execute("SELECT BuildingId, BuildingCode, BuildingName FROM Buildings;").fetchall()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		return buildings

	def ReturnBuildingNameFromFloorId(self, floorId):
		connection=sqlite3.connect("Addresses.db")
		try:
			buildings = connection.execute("SELECT B.BuildingName, BF.FloorNumber, B.BuildingId FROM BuildingFloors AS BF INNER JOIN Buildings AS B ON B.BuildingId = BF.BuildingId WHERE BuildingFloorId = ?;", [floorId]).fetchone()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		return buildings

	def ReturnBuildingCodeNamePairs(self):
		returnBuildingCodeNamePairs = []
		for row in self.ReturnListOfBuildings():
			returnBuildingCodeNamePairs.append(row[2] + " | " + row[1])
		return returnBuildingCodeNamePairs

	def FindBuildingCodeNamePairs(self, criteria):
		connection=sqlite3.connect("Addresses.db")
		try:
			buildings = connection.execute("SELECT BuildingCode, BuildingName FROM Buildings WHERE BuildingName LIKE '%" + criteria + "%' OR BuildingCode LIKE '%" + criteria + "%';").fetchall()
		except: 
			print 'Error connecting to the database...'
		returnBuildingCodeNamePairs = []
		for row in buildings:
			returnBuildingCodeNamePairs.append(row[1] + " | " + row[0])
		return returnBuildingCodeNamePairs

	def ReturnBuildingIdsFromCodeNamePairs(self, codeNamePair):
		bldgName = codeNamePair[0:codeNamePair.find('|')].strip()
		connection=sqlite3.connect("Addresses.db")
		try:
			buildings = connection.execute("SELECT BuildingId FROM Buildings WHERE BuildingName = ?;", [bldgName]).fetchone()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		if (buildings is None):
			return None
		else:
			return buildings[0]

	def ReturnListOfBuildingFloors(self, buildingId):
		connection=sqlite3.connect("Addresses.db")
		try:
			buildingFloors = connection.execute("SELECT BuildingId, FloorNumber, BuildingFloorId FROM BuildingFloors WHERE BuildingId = ?;", [buildingId]).fetchall()
		except:
			print 'Error connecting to the database...'
		connection.close()
		return buildingFloors

	def ReturnFloorNumbers(self, buildingId):
		returnFloorNumbers = []
		for row in self.ReturnListOfBuildingFloors(buildingId):
			returnFloorNumbers.append(row[1])
		return returnFloorNumbers

	def ReturnBuildingFloorId(self, buildingId, floorNumber):
		connection=sqlite3.connect("Addresses.db")
		try:
			buildings = connection.execute("SELECT BuildingFloorId FROM BuildingFloors WHERE BuildingId = ? AND FloorNumber = ?;", (buildingId, floorNumber)).fetchone()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		return buildings[0]

	def ReturnFloorPlans(self, buildingFloorId):
		connection=sqlite3.connect("Addresses.db")
		try:
			floorPlans = connection.execute("SELECT BuildingFloorId, Path, Extent FROM FloorPlans WHERE BuildingFloorId = ?;", [buildingFloorId]).fetchall()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		return floorPlans

	def ReturnPaths(self, buildingFloorId):
		returnPaths = []
		for row in self.ReturnFloorPlans(buildingFloorId):
			returnPaths.append(row[1])
		return returnPaths
	
	def ReturnMapUrlFromFloorId(self, floorNumber):
		connection=sqlite3.connect("Addresses.db")
		try:
			urls = connection.execute("SELECT Path FROM FloorPlans WHERE BuildingFloorId = ?;", [floorNumber]).fetchone()
		except: 
			print 'Error connecting to the database...'
		connection.close()
		return urls[0]

